package my_web.service;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import dao.dao.PreSQL;
import dao.dao.impl.DaoFactory;
import my_web.Interface.LeaveInterface;
import my_web.bean.Emp;
import my_web.bean.Leave;
import my_web.model.LeaveDto;
import my_web.page.Page;
import my_web.page.PageEngine;
import my_web.page.PageParam;
import my_web.page.Imp.PageEngineFactory;
import my_web.page.Imp.PageImp;
public class LeaveService implements LeaveInterface{
	public List<Leave> getAllLeaves() {
		String sql=
				"select Q.*,E.e_name from qjsq_table Q "
				+ "left join Emp_table E on Q.e_id=E.e_id order by Q.qj_id";
		return DaoFactory.getCurrDao().queryBeanList(Leave.class, sql);	
	}
	public Page getLeavePage(PageParam pageParam) {
		return new PageImp(
				new PageEngine(){
					public int getTotal() {
						return DaoFactory.getCurrDao().queryInteger("select count(qj_id) from qjsq_table");
					}
					public List<?> getReclist(int start, int end) {
						String sql=
								"select * "+ 
								"from "+ 
								"(select Q.*,rownum row_ from"+ 
								/*Emp_table */
								"(select Q.*,E.e_name from qjsq_table Q left join Emp_table E on Q.e_id=E.e_id order by Q.qj_id)"
								+"Q where rownum <="+end+")t0 where row_>"+start;
						return DaoFactory.getCurrDao().queryBeanList(Leave.class, sql);
					}
				},
				pageParam
			);
	}
	public void addLeave(LeaveDto dto) {
		DaoFactory.getCurrDao().update("insert into qjsq_table(qj_id,e_id,qj_type,qj_reason,qj_time,qj_days,qj_state)values(?,?,?,?,?,?,?)", 
					dto.getQj_id(),dto.getE_id(),dto.getQj_type(),dto.getQj_reason(),dto.getQj_time(),dto.getQj_days(),"未提交");
	}
	public void removeLeaveById(String qj_id) {
		DaoFactory.getCurrDao().update("delete from qjsq_table where qj_id=?",qj_id);
	}
	public Leave getLeaveById(String qj_id) {
		return DaoFactory.getCurrDao().queryBean(Leave.class,"select Q.*,E.e_name,D.d_name,P.p_name from qjsq_table Q left join Emp_table E on Q.e_id=E.e_id left join Dep_table D on D.d_id=E.d_id left join Pos_table P on P.p_id=E.p_id where Q.qj_id=?",qj_id);
	}
	public void modifyLeave(LeaveDto dto) {
		DaoFactory.getCurrDao().update("update qjsq_table set qj_type=?,qj_reason=?,qj_time=?,qj_days=? where qj_id=?",
				dto.getQj_type(),dto.getQj_reason(),dto.getQj_time(),dto.getQj_days(),dto.getQj_id());
	}
	
	public void removeLeavesById(String[] qj_ids) {
		if(qj_ids==null)return;
		PreSQL psql=DaoFactory.getCurrDao().preSql("delete from qjsq_table where qj_id=?");
		for(String qj_id:qj_ids){
			psql.update(qj_id);
		}
	}
	/**
	 * 动态查询
	 * @param dto
	 * @return
	 */
	public Page DynamicQueryAllLeaves(LeaveDto dto) {
		String sql=
				"select Q.*,E.e_name from qjsq_table Q left join Emp_table E on Q.e_id=E.e_id where 1=1";
		StringBuilder where=new StringBuilder();
		List<Object> paramList=new ArrayList<>();
		if(dto!=null){
			if(dto.getQj_id()!=null && !dto.getQj_id().equals("")){
				where.append(" and qj_id like ?");
				paramList.add("%"+dto.getQj_id()+"%");
			}
			if(dto.getE_name()!=null && !dto.getE_name().equals("")){
				where.append(" and e_name like ?");
				paramList.add("%"+dto.getE_name()+"%");
			}
			if(dto.getQj_type()!=null && !dto.getQj_type().equals("")){
				where.append(" and qj_type like ?");
				paramList.add("%"+dto.getQj_type()+"%");
			}
			if(dto.getQj_reason()!=null && !dto.getQj_reason().equals("")){
				where.append(" and qj_reason like ?");
				paramList.add("%"+dto.getQj_reason()+"%");
			}
			if(dto.getQj_time()!=null && !dto.getQj_time().equals("")){
				where.append(" and qj_time like ?");
				paramList.add("%"+dto.getQj_time()+"%");
			}
			if(dto.getQj_days()!=0 && !(dto.getQj_days()+"").equals("")){
				where.append(" and qj_days like ?");
				paramList.add("%"+dto.getQj_days()+"%");
			}
			if(dto.getQj_state()!=null && !dto.getQj_state().equals("")){
				where.append(" and qj_state like ?");
				paramList.add("%"+dto.getQj_state()+"%");
			}
		}
		where=where.append("order by qj_id");
		try{
			return PageEngineFactory.createOraclePage(Leave.class, DaoFactory.getCurrDao(), sql+where, dto, paramList.toArray());
		}catch(RuntimeException e){
			throw e;
		}
	}
	public Page  QueryAllSubmitedLeaves(LeaveDto dto) {
		//String sql="select Q.*,E.e_name from qjsq_table Q left join Emp_table E on Q.e_id=E.e_id where qj_state='未审核'";
		String sql="select Q.*,E.e_name,D.d_name,P.p_name from qjsq_table Q "
				+ "left join Emp_table E on Q.e_id=E.e_id "
				+ "left join Dep_table D on E.d_id=D.d_id "
				+ "left join Pos_table P on E.p_id=P.p_id "
				+ "where qj_state='未审核' or qj_state='已审核'";
		StringBuilder where=new StringBuilder();
		List<Object> paramList=new ArrayList<>();
		if(dto!=null){
			if(dto.getQj_id()!=null && !dto.getQj_id().equals("")){
				where.append(" and qj_id like ?");
				paramList.add("%"+dto.getQj_id()+"%");
			}
			if(dto.getE_name()!=null && !dto.getE_name().equals("")){
				where.append(" and e_name like ?");
				paramList.add("%"+dto.getE_name()+"%");
			}
			if(dto.getQj_type()!=null && !dto.getQj_type().equals("")){
				where.append(" and qj_type like ?");
				paramList.add("%"+dto.getQj_type()+"%");
			}
			if(dto.getQj_reason()!=null && !dto.getQj_reason().equals("")){
				where.append(" and qj_reason like ?");
				paramList.add("%"+dto.getQj_reason()+"%");
			}
			if(dto.getQj_time()!=null && !dto.getQj_time().equals("")){
				where.append(" and qj_time like ?");
				paramList.add("%"+dto.getQj_time()+"%");
			}
			if(dto.getQj_days()!=0 && !(dto.getQj_days()+"").equals("")){
				where.append(" and qj_days like ?");
				paramList.add("%"+dto.getQj_days()+"%");
			}
			if(dto.getQj_state()!=null && !dto.getQj_state().equals("")){
				where.append(" and qj_state like ?");
				paramList.add("%"+dto.getQj_state()+"%");
			}
		}
		where=where.append("order by qj_id");
		try{
			return PageEngineFactory.createOraclePage(Leave.class, DaoFactory.getCurrDao(), sql+where, dto, paramList.toArray());
		}catch(RuntimeException e){
			throw e;
		}
	}
	public Object QueryAllCheckedLeaves(LeaveDto dto) {
		//String sql="select Q.*,E.e_name  from qjsq_table Q left join Emp_table E on Q.e_id=E.e_id where qj_state='已审核'";
		String sql="select * from "
					+ "(select Q.*,E.e_name,D.d_name,P.p_name "
						+ "from qjsq_table Q "
						+ "left join Emp_table E on Q.e_id=E.e_id "
						+ "left join Dep_table D on E.d_id=D.d_id "
						+ "left join Pos_table P on E.p_id=P.p_id "
						+ "where qj_state='已审核' or qj_state='已备案') "
					+ "where 1=1 ";
		StringBuilder where=new StringBuilder();
		List<Object> paramList=new ArrayList<>();
		if(dto!=null){
			if(dto.getQj_id()!=null && !dto.getQj_id().equals("")){
				where.append(" and qj_id like ?");
				paramList.add("%"+dto.getQj_id()+"%");
			}
			if(dto.getE_name()!=null && !dto.getE_name().equals("")){
				where.append(" and e_name like ?");
				paramList.add("%"+dto.getE_name()+"%");
			}
			if(dto.getD_name()!=null && !dto.getD_name().equals("")){
				where.append(" and d_name like ?");
				paramList.add("%"+dto.getD_name()+"%");
			}
			if(dto.getP_name()!=null && !dto.getP_name().equals("")){
				where.append(" and p_name like ?");
				paramList.add("%"+dto.getP_name()+"%");
			}
			if(dto.getQj_type()!=null && !dto.getQj_type().equals("")){
				where.append(" and qj_type like ?");
				paramList.add("%"+dto.getQj_type()+"%");
			}
			if(dto.getQj_reason()!=null && !dto.getQj_reason().equals("")){
				where.append(" and qj_reason like ?");
				paramList.add("%"+dto.getQj_reason()+"%");
			}
			if(dto.getQj_time()!=null && !dto.getQj_time().equals("")){
				where.append(" and qj_time = ?");
				paramList.add(dto.getQj_time());
			}
			if(dto.getQj_days()!=0 && !(dto.getQj_days()+"").equals("")){
				where.append(" and qj_days like ?");
				paramList.add("%"+dto.getQj_days()+"%");
			}
			if(dto.getQj_state()!=null && !dto.getQj_state().equals("")){
				where.append(" and qj_state like ?");
				paramList.add("%"+dto.getQj_state()+"%");
			}
			if(dto.getSp_date()!=null && !dto.getSp_date().equals("")){
				where.append(" and sp_date = ?");
				paramList.add(dto.getSp_date());
			}
		}
		where=where.append("order by qj_id");
		try{
			return PageEngineFactory.createOraclePage(Leave.class, DaoFactory.getCurrDao(), sql+where, dto, paramList.toArray());
		}catch(RuntimeException e){
			throw e;
		}
	}
	public void modifyLeaveStateToSubmited(String qj_id) {
		DaoFactory.getCurrDao().update("update qjsq_table set qj_state=?where qj_id=?", "未审核",qj_id);	
	}
	public void modifyLeaveStateToDisagree(String qj_id,String sp_r) {
		DaoFactory.getCurrDao().update("update qjsq_table set qj_state=?，sp_r=?,sp_date=?,sp_result=?where qj_id=?", "已审核",sp_r,new Date(),"未通过",qj_id);
	}
	public void modifyLeaveStateToAgree(String qj_id, String sp_r) {
		DaoFactory.getCurrDao().update("update qjsq_table set qj_state=?,sp_r=?,sp_date=?,sp_result=? where qj_id=?","已审核",sp_r,new Date(),"通过",qj_id);	
	}
	public int modifyLeaveToRecord(String qj_id, String ba_r) {
		return DaoFactory.getCurrDao().update("update qjsq_table set qj_state=?,ba_r=?,ba_date=? where qj_id= ?","已备案",ba_r,new Date(),qj_id);	
	}
	/**
	 * 
	 * @Override
	 */
	public Object DynamicQueryAllLeaves(LeaveDto dto, Emp emp) {
		/*String sql=
		"select Q.*,E.e_name from qjsq_table Q left join Emp_table E on Q.e_id=E.e_id where 1=1";*/
		StringBuilder where=new StringBuilder();
		List<Object> paramList=new ArrayList<>();
		String sql = null;
		if("人事部".equals(emp.getD_name())){//人事部
			if("Boss".equals(emp.getP_name())){//人事部boss
				sql="select Q.*,E.e_name,E.d_id from qjsq_table Q left outer join Emp_table E on Q.e_id=E.e_id where E.d_id=? ";
						paramList.add(emp.getD_id());
			}else{//人事部普通员工
				sql="select * from(select Q.*,E.e_name from qjsq_table Q left outer join Emp_table E on Q.e_id=E.e_id where Q.qj_result = '同意' "
					+ "union all select * from(select Q.*,E.e_name from qjsq_table Q left outer join Emp_table E on Q.e_id=E.e_id where Q.e_id=? ";
				paramList.add(emp.getE_id());
			}
		//其他部门
		}else if(emp.getP_name().equals("Boss")){//经理--查看本部门所有员工的请假记录
			sql="select Q.*,E.e_name,E.d_id from qjsq_table Q left outer join Emp_table E on Q.e_id=E.e_id where E.d_id=?";
			paramList.add(0,emp.getD_id());
		}else{//普通员工:只能查看自己的请假记录
			sql="select Q.*,E.e_name from qjsq_table Q left outer join Emp_table E on Q.e_id=E.e_id where Q.e_id=?";
			paramList.add(0,emp.getE_id());
		}
		if(dto!=null){
			if(dto.getQj_id()!=null && !dto.getQj_id().equals("")){
				where.append(" and qj_id like ?");
				paramList.add("%"+dto.getQj_id()+"%");
			}
			if(dto.getE_name()!=null && !dto.getE_name().equals("")){
				where.append(" and e_name like ?");
				paramList.add("%"+dto.getE_name()+"%");
			}
			if(dto.getQj_type()!=null && !dto.getQj_type().equals("")){
				where.append(" and qj_type like ?");
				paramList.add("%"+dto.getQj_type()+"%");
			}
			if(dto.getQj_reason()!=null && !dto.getQj_reason().equals("")){
				where.append(" and qj_reason like ?");
				paramList.add("%"+dto.getQj_reason()+"%");
			}
			if(dto.getQj_time()!=null && !dto.getQj_time().equals("")){
				where.append(" and qj_time = ?");
				paramList.add(dto.getQj_time());
			}
			if(dto.getQj_days()!=0 && !(dto.getQj_days()+"").equals("")){
				where.append(" and qj_days like ?");
				paramList.add("%"+dto.getQj_days()+"%");
			}
			if(dto.getQj_state()!=null && !dto.getQj_state().equals("")){
				where.append(" and qj_state like ?");
				paramList.add("%"+dto.getQj_state()+"%");
			}
		}
		where=where.append("order by qj_id");
		try{
			return PageEngineFactory.createOraclePage(Leave.class, DaoFactory.getCurrDao(), sql+where, dto, paramList.toArray());
		}catch(RuntimeException e){
			throw e;
		}
	}
	/**
	 * @
	 */
	public Object QueryAllSubmitedLeaves(LeaveDto dto,Emp emp) {
		//String sql="select Q.*,E.e_name  from qjsq_table Q left join Emp_table E on Q.e_id=E.e_id where qj_state='已审核'";
		StringBuilder where=new StringBuilder();
		List<Object> paramList=new ArrayList<>();
		String sql = null;
		sql="select Q.*,E.e_name,D.d_name,P.p_name from qjsq_table Q "
				+ "left join Emp_table E on Q.e_id=E.e_id "
				+ "left join Dep_table D on E.d_id=D.d_id "
				+ "left join Pos_table P on E.p_id=P.p_id "
				+ "where qj_state='未审核' and D.d_id=? ";
		paramList.add(0,emp.getD_id());
		
		if(dto!=null){
			if(dto.getQj_id()!=null && !dto.getQj_id().equals("")){
				where.append(" and qj_id like ?");
				paramList.add("%"+dto.getQj_id()+"%");
			}
			if(dto.getE_name()!=null && !dto.getE_name().equals("")){
				where.append(" and e_name like ?");
				paramList.add("%"+dto.getE_name()+"%");
			}
			if(dto.getQj_type()!=null && !dto.getQj_type().equals("")){
				where.append(" and qj_type like ?");
				paramList.add("%"+dto.getQj_type()+"%");
			}
			if(dto.getQj_reason()!=null && !dto.getQj_reason().equals("")){
				where.append(" and qj_reason like ?");
				paramList.add("%"+dto.getQj_reason()+"%");
			}
			if(dto.getQj_time()!=null && !dto.getQj_time().equals("")){
				where.append(" and qj_time = ?");
				paramList.add(dto.getQj_time());
			}
			if(dto.getQj_days()!=0 && !(dto.getQj_days()+"").equals("")){
				where.append(" and qj_days like ?");
				paramList.add("%"+dto.getQj_days()+"%");
			}
			if(dto.getQj_state()!=null && !dto.getQj_state().equals("")){
				where.append(" and qj_state like ?");
				paramList.add("%"+dto.getQj_state()+"%");
			}
		}
		where=where.append(" order by qj_id");
		try{
			return PageEngineFactory.createOraclePage(Leave.class, DaoFactory.getCurrDao(), sql+where, dto, paramList.toArray());
		}catch(RuntimeException e){
			throw e;
		}
	}
	
}
